﻿-- =============================================
-- Author:		<赵洪涛>
-- Create date: <2012-03-12>
-- Description:	<按指定的日期、账号取具体的业务数据>
-- =============================================
CREATE PROCEDURE [dbo].[proc_AB_GetList_Project]
(
		@id int,
		@Iid int,
		@StartDate varchar(10),
		@StartIndex int,
		@EndIndex int,
		@Stext nvarchar(50)
)
AS
BEGIN
	IF(@Iid=0)
	BEGIN
		;WITH list as (Select ROW_NUMBER() OVER (Order By CompanyId, FinanceAuditDate Desc,Id Desc)AS Row
		,CusName
		,ProductName
		,CostPrice
		,TypeName
		,IsFinish
		,(Select Top 1 CompanyName From [Company] Where [Company].Id=CompanyId) As CompanyName
		,IsNull(FinishDate,'1900-01-01') As FinishDate
		,IsNull(FinanceAuditDate,'1900-01-01') As FinanceAuditDate
		,Salesman
		From Project
		Where CompanyId In(Select CompanyId From AB_Interface WHERE syzh=@id) And FinanceAudit=2
		And (Select ProductId From AB_Interface Where id=@id) Like '%{'+Convert(varchar(10),ProductId)+'}%'
		And Convert(nchar(10),FinanceAuditDate,120)=@StartDate
		And (@Stext='' Or CusName like '%'+@Stext+'%')
		)
		
		Select *,(Select Count(0) From list) As RecordCount From list Where Row Between @StartIndex And @EndIndex Order By Row
	END
	ELSE
		BEGIN
			;WITH list as (Select ROW_NUMBER() OVER (Order By FinanceAuditDate Desc,Id Desc)AS Row
				,CusName
				,ProductName
				,CostPrice
				,TypeName
				,IsFinish
				,(Select Top 1 CompanyName From [Company] Where [Company].Id=CompanyId) As CompanyName
				,IsNull(FinishDate,'1900-01-01') As FinishDate
				,IsNull(FinanceAuditDate,'1900-01-01') As FinanceAuditDate
				,Salesman
				From Project
				Where CompanyId In(Select CompanyId From AB_Interface Where id=@id) And FinanceAudit=2
				And (Select ProductId From AB_Interface Where id=@id) Like '%{'+Convert(varchar(10),ProductId)+'}%'
				And Convert(nchar(10),FinanceAuditDate,120)=@StartDate
				And (@Stext='' Or CusName like '%'+@Stext+'%')
			)
			
			Select *,(Select Count(0) From list) As RecordCount From list Where Row Between @StartIndex And @EndIndex Order By Row
		END
END
